﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data.SqlClient;

namespace People
{
    class People
    {
        private static Dictionary<string, int> languages = new Dictionary<string, int>();

        public static Dictionary<string, int> Languages
        {
            get { return People.languages; }
            set { People.languages = value; }
        }

        static void Main()
        {
            XmlReader reader = XmlReader.Create("../../people.xml");
            GetingDataFromXML(reader);


            string fileName = "../../peopleByLanguages.xml";
            Encoding encoding = Encoding.GetEncoding("windows-1251");
            XmlTextWriter writer = new XmlTextWriter(fileName, encoding);
            WritingResultXML(writer);

            //WritingResultInDB();

            foreach (var item in languages)
            {
                Console.WriteLine(item.Key+"-"+item.Value);
            }
        }

        private static void WritingResultInDB()
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; Database=People; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                foreach (var item in languages)
                {
                    SqlCommand cmd = new SqlCommand(
                        "INSERT INTO [Statistics] " +
                        "(LanguageName, NumberOfPeople) VALUES " +
                        "(@language, @numberOfPeople)", dbCon);
                    cmd.Parameters.Add(new SqlParameter("@language", item.Key));
                    cmd.Parameters.Add(new SqlParameter("@numberOfPeople", item.Value));
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private static void WritingResultXML(XmlTextWriter writer)
        {
            using (writer)
            {
                writer.Formatting = Formatting.Indented;
                writer.IndentChar = '\t';
                writer.Indentation = 1;

                writer.WriteStartDocument();
                writer.WriteStartElement("languages");
                foreach (var item in languages)
                {
                    writer.WriteStartElement("language");
                    writer.WriteAttributeString("value", item.Key);
                    writer.WriteStartElement("numberOfPeople");
                    writer.WriteString(item.Value.ToString());
                    writer.WriteEndElement();
                    writer.WriteEndElement();
                }
                writer.WriteEndDocument();
            }
        }

        private static void GetingDataFromXML(XmlReader reader)
        {
            using (reader)
            {
                while (reader.Read())
                {
                    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "person"))
                    {
                        ReadingPersonInfo(reader);
                    }
                }
            }
        }

        private static void ReadingPersonInfo(XmlReader reader)
        {
            while (reader.Read())
            {
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "LastName"))
                {
                    string language = reader.GetAttribute(0);
                    if (!languages.ContainsKey(language))
                    {
                        languages[language] = 0;
                    }
                    languages[language]++;
                }
                if ((reader.NodeType == XmlNodeType.EndElement) && (reader.Name == "person"))
                {
                    return;
                }
            }
        }
    }
}
